﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ThuatToanDAO
{
    public class CategoryDAO
    {
        public CategoryDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        
        /// <summary>
        /// Get string connection
        /// </summary>
        /// <returns>string connection</returns>
        public string GetConnectString()
        {
            // Get connection string from config file
            // string strConnectString = "Data Source=HCD-FRESHER069;Initial Catalog=MockProject_Iteration3;User ID=sa; Password=123456789;";
            string strConnectString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;//Get connection string
            return strConnectString;
        }


        #region Select...

        // <summary>
        /// Get table to dataset
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteSelectSingle(String parameters)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT CategoryID,Name FROM Category WHERE CategoryID = @CategoryID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    //foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(new SqlParameter("CategoryID", parameters)); // Add param to com.Parameters
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }

        // <summary>
        /// Get table to dataset
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteSelectALL()
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT CategoryID,Name FROM Category Order By CategoryID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        public DataSet ExecuteSelectFollowCatagory(String categoryID)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT CategoryID,Name FROM Category WHERE CategoryID LIKE @CategoryID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (categoryID != "") // If parameter is not null
                {
                    //foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(new SqlParameter("CategoryID", categoryID + "%")); // Add param to com.Parameters
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }

        #endregion



        #region Insert...

        // <summary>
        /// Get table to Category
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteInsert(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String insertString = "INSERT INTO Category VALUES (@CategoryID, @Name)";


                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = insertString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }


        #endregion


        #region Update...
        // <summary>
        /// Get table to Category
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteUpdate(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String insertString = "UPDATE Category SET Name = @Name WHERE @CategoryID = -1 OR CategoryID = @CategoryID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = insertString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }

        #endregion


        #region Delete...

        // <summary>
        /// Get table to Category
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteDelete(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String deleteString = "DELETE FROM Category WHERE CategoryID = @CategoryID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = deleteString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }

        #endregion
    }
}
